Data Transformation

Part 2

Table of Contents

• Add new variables with mutate()
• Grouped summaries with summarize()
• Group by with group_by()

<<<<<<< HEAD

Warning message:
In scan(file = file, what = what, sep = sep, quote = quote, dec = dec,  :
  EOF within quoted string

Attaching package: ‘lubridate’

The following object is masked from ‘package:base’:

    date


Attaching package: ‘plotly’

The following object is masked from ‘package:ggplot2’:

    last_plot

The following object is masked from ‘package:stats’:

    filter

The following object is masked from ‘package:graphics’:

    layout

======= >>>>>>> master

Add new variables with mutate()

Besides selecting sets of existing columns, it’s often useful to add new columns that are functions of existing columns. That’s the job of mutate().

mutate() always adds new columns at the end of your dataset, but doesn’t change the original dataframe. If you want to keep the outcome of mutate(), or any of the other functions we discussed in the previous notebook, assign the output to a new or existing object.

# Starting with a smaller number of columns
flights_sml <- select(flights, 
                      year:day, 
                      ends_with("delay"), 
                      distance, 
                      air_time
                     )

flights_sml

mutate(flights_sml,
       gain = dep_delay - arr_delay,
       speed = distance / air_time * 60
      )

Alternatively we could’ve used our piping skills:

flights %>%
  select(year:day, 
         ends_with("delay"), 
         distance, 
         air_time
        ) %>%
  mutate(gain = dep_delay - arr_delay,
         speed = distance / air_time * 60)

Note that you can refer to columns that you’ve just created:

mutate(flights_sml,
       gain = dep_delay - arr_delay,
       hours = air_time / 60,
       gain_per_hour = gain / hours
      )

---

Grouped summaries with summarize()

The last key verb is summarize() (or summarise()). It collapses a data frame to a single row:

# Average delay
summarize(flights, delay = mean(dep_delay, na.rm = TRUE))

What happens if we don’t specify na.rm = TRUE?

group_by()

summarize() is not terribly useful unless we pair it with group_by(). This changes the unit of analysis from the complete dataset to individual groups. Then, when you use the dplyr verbs on a grouped data frame they’ll automatically be applied “by group”. For example, if we applied exactly the same code to a data frame grouped by date, we get the average delay per date:

# Average delay for each day
flights %>%
  group_by(year, month, day) %>%
  summarize(delay = mean(dep_delay, na.rm = TRUE))

Examples

How does the average distance to destination impact the arrival delay on average?

delay <- flights %>% 
  group_by(dest) %>%  # group by destination
  summarize(count = n(),  # counting the number of flights (per destination)
            Distance = mean(distance, na.rm = TRUE),  # average distance (per destination)
            Delay = mean(arr_delay, na.rm = TRUE)  # average delay (per destination)
           ) %>%
  filter(count > 20, dest != "HNL")  # keeping destinations with more than 20 flights that are not "HNL"

delay

p <- ggplot(data = delay, mapping = aes(x = Distance, y = Delay)) +
  geom_point(aes(size = count, color = dest), alpha = 1/3) +
  geom_smooth(se = FALSE) +
  labs(title = "Average distance to destination vs. arrival delay",
      caption = "The circle size shows the number of flights to that destination.")

ggplotly(p + theme(legend.position = "none"))  # Same plot with ggplotly()

`geom_smooth()` using method = 'loess' and formula 'y ~ x'

<<<<<<< HEAD

=======

>>>>>>> master

It looks like delays increase with distance up to ~750 miles and then decrease. Maybe as flights get longer there’s more ability to make up delays in the air?

Can you figure out why we removed “HNL” (Pacific/Honolulu)?

---

Let’s examine if the number of flights during the summer has increased:

flight_count <- flights %>%
  mutate(date = make_datetime(year, month, day)) %>%
  group_by(date) %>%
  summarize(count = n(),  # counting the number of flights (per day)
            delay = mean(arr_delay, na.rm = TRUE)  # average delay (per day)
           )

p <- ggplot(flight_count, aes(date, count)) +
  geom_line() +
  labs(title = "Number of flights out of NYC") +
  theme_classic()

ggplotly(p)

<<<<<<< HEAD

=======

>>>>>>> master

Interestingly, all of these dips are weekends. Let’s check this by creating a bar chart:

flights %>%
  mutate(date = make_datetime(year, month, day),
        weekday = wday(date, label = TRUE)) %>%
  group_by(weekday) %>%
  summarize(count = n(),  # counting the number of flights (per weekday)
            delay = mean(arr_delay, na.rm = TRUE)  # average delay (per weekday)
           ) %>% 
  ggplot(aes(weekday, count)) + 
    geom_bar(stat="identity", width = 0.5) + 
    labs(title="Number of flights per dat of week",
        x = "Day of Week",
        y = "Count") +
    theme_classic() +
    theme(axis.text.x = element_text(angle=65, vjust=0.6))